/****** Object:  Stored Procedure dbo.asi_AccessPermission    Script Date: 7/3/2003 4:58:02 PM ******/
CREATE  PROC asi_AccessPermission
    @accessKey uniqueidentifier,
    @userKey uniqueidentifier
    AS
BEGIN
    SET NOCOUNT ON

    CREATE TABLE #permission (Permission int NULL)

    INSERT    #permission (Permission)
    VALUES    (0)

    --Permissions assigned to users    
    INSERT    #permission
    SELECT    DISTINCT AccessItem.Permission
     FROM    AccessItem
     WHERE    AccessItem.AccessKey = @accessKey
       AND    AccessItem.UserKey = @userKey
       AND  NOT EXISTS(SELECT 1 FROM #permission where Permission = AccessItem.Permission)
    
    --Bypass if full control already established
    IF NOT EXISTS(SELECT 1 FROM #permission where Permission = 1)
    BEGIN
        --Permissions assigned to roles
        INSERT    #permission
        SELECT    DISTINCT AccessItem.Permission
         FROM    AccessItem
                INNER JOIN UserRole on AccessItem.RoleKey = UserRole.RoleKey
         WHERE    AccessItem.AccessKey = @accessKey
           AND    UserRole.UserKey = @userKey
           AND  NOT EXISTS(SELECT 1 FROM #permission where Permission = AccessItem.Permission)
    END
    
    --Bypass if full control already established
    IF NOT EXISTS(SELECT 1 FROM #permission where Permission = 1)
    BEGIN
        --Permissions assigned to groups
        INSERT    #permission
        SELECT    DISTINCT AccessItem.Permission
         FROM    AccessItem
                INNER JOIN ListItem on AccessItem.GroupKey = ListItem.ListKey
                INNER JOIN GroupItem on ListItem.ListItemKey = GroupItem.GroupItemKey
         WHERE    AccessItem.AccessKey = @accessKey
           AND    ListItem.ObjectKey = @userKey
           AND    GroupItem.IsDisabled = 0
           AND    (GroupItem.EffectiveDate <= getdate() OR GroupItem.EffectiveDate IS NULL)
           AND    (GroupItem.ExpirationDate >= getdate() OR GroupItem.ExpirationDate IS NULL)
           AND  NOT EXISTS(SELECT 1 FROM #permission where Permission = AccessItem.Permission)
    END
    
    SELECT    SUM(Permission)
     FROM    #permission
    
    DROP TABLE #permission

    SET NOCOUNT OFF
END

GO